# encoding:utf-8
import json
import os
import pickle
import uuid
from datetime import datetime

import pandas as pd
from loguru import logger
import pymongo
from bson import ObjectId, json_util
from flask import Flask, request, jsonify, g
from flask_apscheduler import APScheduler  # 主要插件
from util.pool_conn_utils import pool_util
from flask_cors import *  # 导入模块
from flask import send_file, send_from_directory
import io
from sqlalchemy import create_engine
#
from util.BaseMiddleWare import ResponseResult
from config import config
from view import view_hello

#
app = Flask(__name__)
CORS(app, supports_credentials=True, resources="/*")  # 设置跨域
# from .reduce import reduce
# app.register_blueprint(reduce)
app.config['DEBUG'] = True  # 开启 debug
app.config['UPLOAD_FOLDER'] = 'doc/'
# app.config['JSONIFY_MIMETYPE'] = "application/json;charset=utf-8"  # 指定浏览器渲染的文件类型，和解码格式；


# 从配置类拿配置
config_class = config.config_class()
db = pool_util(config_class.get_from_conn_list())
sqlalchemy_str = config_class.get_sqlalchemy_str()
engine = create_engine(sqlalchemy_str)  # fixme 地址改到配置类里


def log_and_print(info):
    # print(info)
    logger.info(info)


#############################################################
@app.route('/hello', methods=['get', 'post'])
def select():
    return view_hello.hello()


# generate_timestr
def gts():
    return datetime.now().strftime('%Y%m%d%H%M%S')


def increament_id():
    while 1:
        for i in range(1111, 9999):
            yield i


@app.route('/import', methods=['post'])
def import_excel():
    file = request.files['file']
    raw_file_name = file.filename
    if (not raw_file_name) or (raw_file_name.find('.xlsx') < 0):
        return
    filename = 'upload_' + gts() + '.xlsx'
    save_file_name = os.path.join(app.config['UPLOAD_FOLDER'], filename)
    file.save(save_file_name)
    # 解析excel
    df = pd.read_excel(save_file_name)
    df['id'] = uuid.uuid1()
    g = increament_id()
    df['id'] = df['id'].apply(lambda x: gts() + str(next(g)))
    df.to_sql('emp', con=engine, if_exists='append', index=False)
    return ResponseResult.sucess('上传成功')


@app.route('/update_state', methods=['get'])
def update_state():
    name = request.args.get('name')
    state = request.args.get('state')
    sql = """update emp set 当前状态 = '%s' where 姓名 = '%s' """ % (state, name)
    db.executemany2_sql([sql])
    return ResponseResult.sucess('修改成功')


@app.route('/delete', methods=['get', 'post'])
def delete():
    name = request.args.get('name')
    sql = """delete from emp where 姓名 = '%s' """ % (name)
    db.executemany2_sql([sql])
    return ResponseResult.sucess('删除成功')


@app.route('/list', methods=['get', 'post'])
def list():
    page_num = request.args.get('pageNum') or 0
    page_num = int(page_num)
    page_size = request.args.get('pageSize') or 20
    page_size = int(page_size)
    s = request.args.get('s')
    sql = """SELECT * FROM emp WHERE id IN( select MAX(id) as id from emp GROUP by 姓名) $s$ ORDER BY id DESC """
    if s:
        like_case = "'%" + str(s) + "%'"
        where_case = """AND (员工手机号 like $like$ 
        OR 姓名 like $like$ 
        OR 当前状态 like $like$ 
        OR 入职时间 like $like$ 
        OR 毕业时间 like $like$ 
        OR 学历 like $like$ 
        OR 岗位 like $like$ 
        OR 职务 like $like$ 
        OR 所属项目 like $like$ 
        OR 所属部门 like $like$ 
        OR 行方级别 like $like$ 
        OR 员工关系 like $like$ 
        OR 员工关系手机号 like $like$ 
        OR 待岗开始时间 like $like$ 
        OR 待岗结束时间 like $like$ 
        OR 工作BASE地 like $like$)"""
        where_case = where_case.replace('$like$', like_case)
        sql = sql.replace('$s$', where_case)
    else:
        sql = sql.replace('$s$', '')
    count_sql = sql.replace('*', 'count(0) as total')
    sql += """limit %s,%s""" % (page_num * page_size, page_size)
    all = db.fetch_all(sql)
    count_0 = db.fetch_one(count_sql)
    #
    result_dic = {}
    total = count_0['total']
    result_dic['page'] = {'pageNum': page_num, 'total': total,
                          'maxPage': total // page_size if total % page_size == 0 else total // page_size + 1}
    result_dic['list'] = all
    #
    return ResponseResult.sucess(result_dic)


@app.route('/templet', methods=['get', 'post'])
def download_templet():
    return send_from_directory('doc', '上传模板.xlsx', as_attachment=True)


if __name__ == '__main__':
    app.run(host='0.0.0.0', port=5001)
